package system.servlet;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;


import framework.core.DBConnection;
import framework.core.DBEngine;
import framework.core.DBResult;
import framework.core.DateUtil;

public class ReportBigBusAll extends HttpServlet{
	private static final long serialVersionUID = 1;// 仅为不让本类出现警告 unnecessary
	public static final String REGEXP_SHORT_DATE = "yyyy-MM-dd";
	private String REPAIR_CODE = "('01310010001','01310010002','01310010003','01310010004','01310010005','01310010006','01310010007','01310010008','01310010009')";// 虚拟工单号
	public void doPost(HttpServletRequest request, HttpServletResponse response){
		// 封装数据并返回给客户端
		// 判断报表生成的结果
		try {
			request.setCharacterEncoding("UTF-8");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		response.setContentType("text/html;charset=UTF-8");
		String title = request.getParameter("title");
		String type = request.getParameter("type");
		String path = "";
		boolean isInsert = false;
		String insertSql = "";
		
		/**  跳转页面
		 * 1.高消耗材料费类型统计 2. 
		 */
		if(type.equals("1")){
			if ("1".equals(title)) {
				path = "/system/bigBusReport/UpperMoney.jsp";
			}else if("2".equals(title)){
				path = "/system/bigBusReport/TenBusMoney.jsp";
			}else if("3".equals(title)){
				path = "/system/bigBusReport/TenBusCount.jsp";
			}else if("4".equals(title)){
				path = "/system/bigBusReport/UpperTenProduct.jsp";
			}else if("5".equals(title)){
				path = "/system/bigBusReport/OnclickTenBus.jsp";
			}else if("6".equals(title)){
				path = "/system/bigBusReport/OnclickTenProduct.jsp";
			}else if("7".equals(title)){
				path = "/system/bigBusReport/TypequalityQuary.jsp";
			}else if("8".equals(title)){
				path = "/system/bigBusReport/OneYearNoStore.jsp";
			}else if("9".equals(title)){
				path = "/system/bigBusReport/BigOut.jsp";
			}else if("10".equals(title)){
				path = "/system/bigBusReport/SingleSupplement.jsp";
			}
	 
			try {
				getServletConfig().getServletContext()
						.getRequestDispatcher(path).forward(request, response);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		//执行报表
		else if(type.equals("2")){
			
			String tDateString = new DateUtil().getFullString();
			String tYear = tDateString.substring(0, 4);
			String tMonth = tDateString.substring(5, 7);
			String tDate = tDateString.substring(8, 10);
			String tHour = tDateString.substring(11, 13);
			String tMin = tDateString.substring(14, 16);
			String tSec = tDateString.substring(17, 19);
			String fileName = "GW_REPORT_" + tYear + "_" + tMonth + "_" + tDate
					+ "_" + tHour + "_" + tMin + "_" + tSec;
			
			
			String time = getCurrentDate(REGEXP_SHORT_DATE);
			String year = time.substring(0, 4);
			String moon = time.substring(5, 7);
			String day = time.substring(8, 10);
			Map colMap = new TreeMap();
			Map colNameMap = new TreeMap();
			Map colPd = new TreeMap();
			String sql = "";
			Vector vec2 = new Vector();
			//表头标题
			String titleTop = "";
			if ("1".equals(title)) {
				titleTop = "高消耗材料费类型统计";
				
				sql = ""
					+ "select b3.type_name,sum(b2.product_money) as ss,sum(case when cast(substr(b1.out_time,1,4) as number(10))= cast('"
					+ year
					+ "' as number(10)) then b2.product_money else 0 end ) as ss1,"
					+ "sum(case when cast(substr(b1.out_time,1,4) as number(10))= cast('"
					+ year
					+ "' as number(10))-1 then b2.product_money else 0 end ) as ss2,b3.product_type "
					+ "from t_out_head b1, t_out_sub b2,(select a1.product_code,a2.type_name,a1.product_type from "
					+ "t_product_bid a1, t_product_type_manager a2 where a1.product_type=a2.product_type "
					+ "and a2.status='1' and a2.upper_type='2000' and a1.status='1') b3 where b1.out_bill_code = b2.out_bill_code and b2.product_code=b3.product_code "
					+ "and cast(substr(b1.out_time,1,4) as number(10))>= cast('"
					+ year
					+ "' as number(10))-1 and b1.status='1' and b2.status='1' "
					+ "group by b3.type_name,b3.product_type order by ss desc "
					+ "";
				colMap.put("0","type_name");
				colMap.put("1","ss");
				colMap.put("2","ss1");
				colMap.put("3","ss2");
				colNameMap.put("0","产品类型");
				colNameMap.put("1","今年去年总额");
				colNameMap.put("2","今年总金额");
				colNameMap.put("3","去年总金额");
				colPd.put("0","文本");
				colPd.put("1","小数保留2");
				colPd.put("2","小数保留2");
				colPd.put("3","小数保留2");
				
			}else if("2".equals(title)){
				titleTop = "自编号前三位金额统计";
				sql = "select b1.tc,b1.cou1 from "
				+ "( select substr(a1.customer_bus_code,1,3) as tc,sum(a1.out_money) as cou1 from t_out_head a1 where a1.customer_bus_code is not null and a1.status='1' and substr(a1.out_time,1,4)='"+year+"' group by substr(a1.customer_bus_code,1,3)) b1 "
				+ "order by cou1 desc";
				colMap.put("0","tc");
				colMap.put("1","cou1");
				colNameMap.put("0","前三位编码");
				colNameMap.put("1","出库金额");
				colPd.put("0","文本");
				colPd.put("1","小数保留2");
			}else if("3".equals(title)){
				titleTop = "自编号前三位数量统计";
				sql = "select b1.tc,b1.cou2 from "
				+ "( select substr(a1.customer_bus_code,1,3) as tc,count(*) as cou2 from t_out_head a1 where a1.customer_bus_code is not null and a1.status='1' and substr(a1.out_time,1,4)='"+year+"' group by substr(a1.customer_bus_code,1,3)) b1 "
				+ "order by cou2 desc";
				colMap.put("0","tc");
				colMap.put("1","cou2");
				colNameMap.put("0","前三位编码");
				colNameMap.put("1","出库数量");
				colPd.put("0","文本");
				colPd.put("1","小数保留2");
			}else if("4".equals(title)){
				String productType = request.getParameter("productType");
				titleTop = "类型统计产品材料费";
				String tj = "";
				String tj2 = "";
				if(productType!=null&&!productType.equals("")){
				tj2 =" and a2.type_name like '%"+productType+"%' ";
				}
				sql = "select c1.*,c2.name from( "
				+ "select b3.product_code,sum(case when cast(substr(b1.out_time,1,4) as number(10))= cast('"
				+ year
				+ "' as number(10)) then b2.product_money else 0 end) ss,sum(case when cast(substr(b1.out_time,1,4) as number(10))= cast('"
				+ year
				+ "' as number(10))-1 then b2.product_money else 0 end) ss2,sum(b2.product_money) ss3 from t_out_head b1, t_out_sub b2,"
				+ "(select a1.product_code,a2.type_name,a1.product_type from t_product_bid a1, t_product_type_manager a2 "
				+ "where a1.product_type=a2.product_type and a2.status='1' and a2.upper_type='2000' and a1.status='1' "+tj2+") b3 "
				+ "where b1.out_bill_code = b2.out_bill_code and b2.product_code=b3.product_code and "
				+ tj
				+ " "
				+ "cast(substr(b1.out_time,1,4) as number(10))>= cast('"
				+ year
				+ "' as number(10))-1"
				+ " and b1.status='1' and b2.status='1' "
				+ "group by b3.product_code ) c1,t_product c2 where c1.product_code=c2.concentricity_code and c2.status='1' order by c1.ss3 desc";
				colMap.put("0","product_code");
				colMap.put("1","name");
				colMap.put("2","ss3");
				colMap.put("3","ss");
				colMap.put("4","ss2");
				colNameMap.put("0","产品编码");
				colNameMap.put("1","产品名称");
				colNameMap.put("2","今年去年总额");
				colNameMap.put("3","今年总金额");
				colNameMap.put("4","去年总金额");
				colPd.put("0","文本");
				colPd.put("1","文本");
				colPd.put("2","小数保留2");
				colPd.put("3","小数保留2");
				colPd.put("4","小数保留2");
			}else if("5".equals(title)){
				String tj2 = "";
				tj2 = "substr(a1.out_time,1,4)='" + year + "' and ";
				String myCode = request.getParameter("myCode");
				titleTop = "统计自编号金额";
				sql = "select b1.*, b2.company_name from( "
				+ "select customer_bus_code,sum(out_money) as out_money,  warehouse_code ,warehouse_name from t_out_head a1 "
				+ "where a1.status='1' and "
				+ tj2
				+ " a1.customer_bus_code like '"
				+ myCode
				+ "%' group by customer_bus_code,warehouse_code,warehouse_name ) b1, w_company_warehouse b2 where "
				+ "b1.warehouse_code= b2.warehouse_code and b2.status='1' order by b1.out_money desc";
				colMap.put("0","customer_bus_code");
				colMap.put("1","company_name");
				colMap.put("2","warehouse_name");
				colMap.put("3","out_money");
				colNameMap.put("0","车辆自编号");
				colNameMap.put("1","所属公司");
				colNameMap.put("2","仓库名称");
				colNameMap.put("3","出库金额");
				colPd.put("0","文本");
				colPd.put("1","文本");
				colPd.put("2","文本");
				colPd.put("3","小数保留2");
			}else if("6".equals(title)){
				String myCode = request.getParameter("myCode");
				String tj3 = "substr(a1.out_time,1,4)='" + year + "' and ";
				titleTop = "自编号统计产品金额";
				sql = "select b1.su,b2.concentricity_code,b2.name,b1.su2 from("
				+ "select a2.product_code, sum(a2.product_money)as su,sum(a2.product_count)as su2,a1.warehouse_code from t_out_head a1, t_out_sub a2 where "
				+ "a1.status='1' and a2.status='1' and "
				+ tj3
				+ " a1.customer_bus_code like '%"
				+ myCode
				+ "%' and a1.out_bill_code=a2.out_bill_code group by a2.product_code,a1.warehouse_code ) b1,"
				+ "t_product b2,w_company_warehouse b3 where b1.warehouse_code= b3.warehouse_code and b1.product_code = b2.concentricity_code and b2.status='1' and b3.status='1' order by b1.su desc";
				colMap.put("0","concentricity_code");
				colMap.put("1","name");
				colMap.put("2","su");
				colMap.put("3","su2");
				colNameMap.put("0","产品编码");
				colNameMap.put("1","产品名称");
				colNameMap.put("2","出库金额");
				colNameMap.put("3","出库数量");
				colPd.put("0","文本");
				colPd.put("1","文本");
				colPd.put("2","小数保留2");
				colPd.put("3","小数保留2");
			}else if("7".equals(title)){
				titleTop = "保内出库";
				isInsert = true;
				//String time2 = getUpDay(8000);
				String nextTime = getUpDay(1);
				insertSql = " insert into  qaa2 (SELECT out_time, product_code, customer_bus_code, quality_assure_period,"
				+ "  out_bill_code, warehouse_code,"
				+ " TO_CHAR (DAY1, 'yyyy-mm-dd hh24:mi:ss') next_time"
				+ "  FROM (SELECT out_time, product_code, customer_bus_code,"
				+ " quality_assure_period, out_bill_code, warehouse_code,"
				+ "  TO_DATE (out_time, 'yyyy-mm-dd hh24:mi:ss')"
				+ "   - quality_assure_period DAY1"
				+ " FROM ( SELECT CAST (a.out_time AS VARCHAR2 (20)) out_time, b.product_code,"
				+ "        a.customer_bus_code, to_number(c.quality_assure_period) quality_assure_period, a.out_bill_code,a.warehouse_code,c.quality_assure_period next_time "
				+ " FROM t_out_head a, t_out_sub b, t_product c"
				+ " WHERE a.out_time > '"
				+ time
				+ "'  AND a.out_time < '"
				+ nextTime
				+ "'  and a.REPAIR_BILL_CODE NOT IN "
				+ REPAIR_CODE
				+ "  AND a.customer_bus_code IS NOT NULL AND a.out_bill_code = b.out_bill_code  AND b.status = 1   AND b.product_code = c.concentricity_code"
				+ "  AND isnumeric(c.quality_assure_period)>0 and c.quality_assure_period>'0') ddd)dddd)";
				
				
				String warehouseCode = warehouseCode = " like '0000%' ";
				sql = "select total_a.out_time,c.product_type,total_a.product_code,c.product_name,total_a.quality_assure_period,total_a.warehouse_code,total_a.counts from ("
				+ "SELECT qaa2.out_time,qaa2.product_code,qaa2.out_bill_code,qaa2.warehouse_code,qaa2.customer_bus_code,qaa2.quality_assure_period,sum(b.product_count) counts "
				+ "FROM t_out_head a, t_out_sub b, qaa2 "
				+ "WHERE a.out_time<qaa2.out_time   and a.out_time>  qaa2.next_time and a.out_bill_code = b.out_bill_code AND b.status = 1  and a.REPAIR_BILL_CODE NOT IN "
				+ REPAIR_CODE
				+ "AND b.product_code = qaa2.product_code and a.customer_bus_code=qaa2.customer_bus_code and a.warehouse_code=qaa2.warehouse_code "
				+ "group by qaa2.out_time,qaa2.product_code,qaa2.out_bill_code,qaa2.warehouse_code,qaa2.customer_bus_code,qaa2.quality_assure_period ) total_a ,t_product_bid c where total_a.warehouse_code "
				+ warehouseCode
				+ "and total_a.product_code = c.product_code  order by out_time desc";
				colMap.put("0","product_code");
				colMap.put("1","product_name");
				colMap.put("2","quality_assure_period");
				colMap.put("3","counts");
				colMap.put("4","out_time");
				colNameMap.put("0","产品编码");
				colNameMap.put("1","产品名称");
				colNameMap.put("2","质保期");
				colNameMap.put("3","数量");
				colNameMap.put("4","出库时间");
				colPd.put("0","文本");
				colPd.put("1","文本");
				colPd.put("2","文本");
				colPd.put("3","小数保留2");
				colPd.put("4","文本");
			}else if("8".equals(title)){
				titleTop = "未动态库存";
				String time2 = getUpDay(-1 * 360);
				sql = "select c1.*,c2.name from (select * from( select a1.warehouse_name,a1.product_code,a1.product_price,a1.product_count,a1.product_money,"
						+ "floor(to_date('"
						+ time
						+ "','yyyy-mm-dd hh24:mi:ss')-to_date(a1.last_update_time,'yyyy-mm-dd hh24:mi:ss')) as dzdate,cast(a1.last_update_time as varchar2(20)) as uptime "
						+ "from t_product_storage a1,t_product_bid a2,zt_all_type a3,w_company_warehouse a4 where a1.last_update_time < '"
						+ time2
						+ "' and a1.status = '1' and a1.balance_mode ='1' and a1.product_money>0 and a1.product_count>0 "
						+ "and a2.product_type = a3.product_type and a1.product_code = a2.product_code and a2.status='1' and a3.status='1' and a3.type_enum='2' and a1.warehouse_code=a4.warehouse_code ) "
						+ ")c1,t_product c2 where c1.product_code=c2.concentricity_code and c2.status='1' order by c1.dzdate";
				colMap.put("0","warehouse_name");
				colMap.put("1","product_code");
				colMap.put("2","name");
				colMap.put("3","product_count");
				colMap.put("4","product_money");
				colNameMap.put("0","仓库名称");
				colNameMap.put("1","产品编码");
				colNameMap.put("2","产品名称");
				colNameMap.put("3","数量");
				colNameMap.put("4","金额");
				colPd.put("0","文本");
				colPd.put("1","文本");
				colPd.put("2","文本");
				colPd.put("3","小数保留2");
				colPd.put("4","小数保留2");
			}else if("9".equals(title)){
				titleTop = "大批量出库";
				String time2 = getUpDay(1);
				String count = request.getParameter("count");
				String price = request.getParameter("money");
				if (count == null || count.equals("")) {
					count = "0";
				}
				if (price == null || price.equals("")) {
					price = "0";
				}
				sql = "select c1.*,c2.name from (select a1.warehouse_name,a2.product_code,a2.product_count,a2.product_money,cast(a1.out_time as varchar2(20)) as outtime "
						+ "from  t_out_head a1,t_out_sub a2,t_product_bid a3,zt_all_type a4 "
						+ "where a1.out_bill_code = a2.out_bill_code and a2.product_count>"
						+ count
						+ " and a2.product_price > "
						+ price
						+ " and a1.status='1' and a2.status='1' "
						+ "and a3.product_type=a4.product_type and a3.product_code = a2.product_code and a3.status='1' and a4.status='1' and a4.type_enum='3' and a1.out_time>'"+time+"' and a1.out_time<'"+time2+"' ) c1,t_product c2 "
						+ "where c1.product_code=c2.concentricity_code and c2.status='1' order by c1.outtime desc";
				colMap.put("0","warehouse_name");
				colMap.put("1","product_code");
				colMap.put("2","name");
				colMap.put("3","product_count");
				colMap.put("4","product_money");
				colMap.put("5","outtime");
				colNameMap.put("0","仓库名称");
				colNameMap.put("1","产品编码");
				colNameMap.put("2","产品名称");
				colNameMap.put("3","数量");
				colNameMap.put("4","金额");
				colNameMap.put("5","出库时间");
				colPd.put("0","文本");
				colPd.put("1","文本");
				colPd.put("2","文本");
				colPd.put("3","小数保留2");
				colPd.put("4","小数保留2");
				colPd.put("5","文本");
			}else if("10".equals(title)){
				titleTop = "补单";
				String time2 = getUpDay(1);
				sql = "select c1.*,c2.name from(select a1.warehouse_name,a2.product_code, cast(a1.create_time as varchar(20)),cast(a1.out_time as varchar(20)) as out_time,a2.product_count "
						+ "from t_out_sub a2,t_out_head a1,t_product_bid a3,zt_all_type a4 where a2.out_bill_code = a1.out_bill_code and substr(a1.create_time,1,10) <>substr(a1.out_time,1,10) "
						+ "and a1.status='1' and a2.status = '1' and a3.product_type=a4.product_type and a3.product_code = a2.product_code and a3.status='1' and a4.status='1' and a4.type_enum='4'  and a1.out_time>'"+time+"' and a1.out_time<'"+time2+"' ) c1,t_product c2  "
						+ "where c1.product_code=c2.concentricity_code and c2.status='1' order by c1.out_time desc";
				colMap.put("0","warehouse_name");
				colMap.put("1","product_code");
				colMap.put("2","name");
				colMap.put("3","product_count");
				colMap.put("4","out_time");
				colNameMap.put("0","仓库名称");
				colNameMap.put("1","产品编码");
				colNameMap.put("2","产品名称");
				colNameMap.put("3","数量");
				colNameMap.put("4","出库时间");
				colPd.put("0","文本");
				colPd.put("1","文本");
				colPd.put("2","文本");
				colPd.put("3","小数保留2");
				colPd.put("4","文本");
			}
			
			if(isInsert){
				vec2 = startConnections(sql,colMap,request,response,isInsert,insertSql);
			}else{
				vec2 = startConnections(sql,colMap,request,response);
			}
			writeExcel_ThroughAll(fileName,vec2,titleTop,request,response,colNameMap,colPd);
		}
			
		
	}
	public void doGet(HttpServletRequest request, HttpServletResponse response)
	throws ServletException {
		doPost(request, response);
	}
	
	/****************************时间处理方法 start ************************/
	private String getUpDay(int num) {
		Date date = new Date();
		date = addDays(date, num);
		return formatByShort(date);
	}
	public static String formatByShort(Date date){
		SimpleDateFormat dateFormat = new SimpleDateFormat(REGEXP_SHORT_DATE);
		return dateFormat.format(date);
	}
	public static Date addDays(Date date, int days){
		Calendar calendar = Calendar.getInstance();		
		calendar.setTime(date);
		calendar.add(Calendar.DATE, days);
		return calendar.getTime();
	}
	public static String getCurrentDate(String aFormat) {
        String tDate = new SimpleDateFormat(aFormat).format(new java.util.Date(System.currentTimeMillis()));
        return tDate;
    }
	/****************************    end    ************************/
	
	
	
	/**
	 * 报表 传递集合 统一方法
	 */
	
	public static Vector startConnections(String sql,Map colMap,HttpServletRequest request,HttpServletResponse response){
		
		Vector vec2 = new Vector();
		Vector vec1 = new Vector();
		DBConnection con = null;
		DBResult dbrs = null;
		ResultSet rs = null;
	try {
		con = DBConnection.open(false);
		dbrs = DBEngine.doSelect(con, sql);
		rs = dbrs.getRs();
		while (rs.next()) {
			Set set = colMap.keySet();
			String[] values = new String[set.size()];
			Iterator it = set.iterator();  
			while(it.hasNext()){
				String key = it.next()+"";
				int i = Integer.parseInt(key);
				values[i] = rs.getString(colMap.get(key)+"");
				
			}
			vec1.add(values);
		}

		vec2.add(vec1);
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (dbrs != null) {
				dbrs.clear();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	return vec2;
	}
	
	
	/**
	 * 报表 传递集合 统一方法
	 */
	
	public static Vector startConnections(String sql,Map colMap,HttpServletRequest request,HttpServletResponse response,boolean isInsert,String insertSql){
		
		Vector vec2 = new Vector();
		Vector vec1 = new Vector();
		DBConnection con = null;
		DBResult dbrs = null;
		ResultSet rs = null;
		Connection con2 = null;
		PreparedStatement ps = null;
	try {
		con = DBConnection.open(true);
		if(isInsert){
	        	con2 = con.getConnection();
	            ps = con2.prepareStatement(insertSql);
	            ps.executeUpdate();
	            con2.commit();
	        	//int num = stmt.executeUpdate(insertSql);
	            //con2.commit();
		}
		//con = DBConnection.open(false);
		//dbrs = DBEngine.doSelect(con, sql);
		
		con = DBConnection.open(false);
		dbrs = DBEngine.doSelect(con, sql);
		rs = dbrs.getRs();
		while (rs.next()) {
			Set set = colMap.keySet();
			String[] values = new String[set.size()];
			Iterator it = set.iterator();  
			while(it.hasNext()){
				String key = it.next()+"";
				int i = Integer.parseInt(key);
				values[i] = rs.getString(colMap.get(key)+"");
				
			}
			vec1.add(values);
		}

		vec2.add(vec1);
		ps = con2.prepareStatement("delete from qaa2");
		ps.executeUpdate();
		con2.commit();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            
            e.printStackTrace();
        }
		try {
			if (con2 != null) {
				con2.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (dbrs != null) {
				dbrs.clear();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		try {
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	return vec2;
	}
	
	/*  执行更新SQL语句   */
    public static boolean executeUpdateSql(Connection con,String sql) {
        PreparedStatement ps = null;
        if(con == null)
            return false;
        if(sql == null || sql == "")
            return false;
        try {
            ps = con.prepareStatement(sql);
            int num = ps.executeUpdate();
            con.commit();
            return true;
        } catch (SQLException e) {
            
            e.printStackTrace();
            return false;
        }finally{
            try {
                if(ps != null)
                    ps.close();
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
        }
    }
	
	
	//带入条件 返回 sql 或hql  等于
	public static String equalsWhere(Map tjMap){
		String where = "";
		String tableyy = "";
		if(tjMap.get("tableName")!=null){
			tableyy = tjMap.get("tableName")+".";
		}
		tjMap.remove("tableName");
		Set set = tjMap.keySet();
		while(set.iterator().hasNext()){
			if(tjMap.get(set.iterator())!=null&&!(tjMap.get(set.iterator())+"").equals("")&&!tjMap.get(set.iterator()).equals("null")){
				where+= " and "+tableyy+set.iterator()+"='"+tjMap.get(set.iterator())+"' ";
			}
		}
		return where;
	}
	//带入条件 返回 sql 或hql  模糊%
	public static String likeWhere(Map tjMap){
		String where = "";
		String tableyy = "";
		if(tjMap.get("tableName")!=null){
			tableyy = tjMap.get("tableName")+".";
		}
		tjMap.remove("tableName");
		Set set = tjMap.keySet();
		while (set.iterator().hasNext()) {
			if(tjMap.get(set.iterator())!=null&&!(tjMap.get(set.iterator())+"").equals("")&&!tjMap.get(set.iterator()).equals("null")){
				where+= " and "+tableyy+set.iterator()+" like '%"+tjMap.get(set.iterator())+"%' ";
			}
		}
		return where;
	}
	
	
	
	// 文本
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,
			String alaValueue,String[] values) {
		HSSFCellStyle contextStyle = aWb.createCellStyle();
		HSSFDataFormat format = aWb.createDataFormat();
		contextStyle.setDataFormat(format.getFormat("@"));
		HSSFCell tCell = aRow.createCell(column);
		tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		tCell.setCellStyle(contextStyle);
		tCell.setCellValue(alaValueue);
		tCell.setCellType(HSSFCell.CELL_TYPE_STRING);
		
		
		return tCell;
	}

	// 整数
	private HSSFCell createCell2(HSSFWorkbook aWb, HSSFRow aRow, short column,
			String alaValueue,String[] values) {
		HSSFCellStyle contextStyle = aWb.createCellStyle();
		// contextStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
		HSSFCell tCell = aRow.createCell(column);
		tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		tCell.setCellStyle(contextStyle);
		tCell.setCellValue(Double.parseDouble(alaValueue));
		return tCell;
	}

	// 2位小数
	private HSSFCell createCell3(HSSFWorkbook aWb, HSSFRow aRow, short column,
			String alaValueue,String[] values) {
		HSSFCellStyle contextStyle = aWb.createCellStyle();
		contextStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		HSSFCell tCell = aRow.createCell(column);
		tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		tCell.setCellStyle(contextStyle);
		tCell.setCellValue(Double.parseDouble(alaValueue));
		
		
		return tCell;
	}

	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,
			String aValue, short aAlign, boolean border) {
		HSSFCell tCell = aRow.createCell(column);
		tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		tCell.setCellValue(aValue);
		HSSFCellStyle tCellStyle = aWb.createCellStyle();
		tCellStyle.setAlignment(aAlign);
		tCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		if (border) {
			tCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			tCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			tCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			tCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			tCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
			tCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
			tCellStyle.setRightBorderColor(HSSFColor.BLACK.index);
			tCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		}
		tCell.setCellStyle(tCellStyle);
		return tCell;
	}
	
	
	
	
	/**
	 * 							处理报表方法 
	 */
	
	
	
	/** EXCEL 处理 报表 安全库存报表 */
	private void writeExcel_ThroughAll(
			String fileName, Vector vec, String title1,HttpServletRequest request,
			HttpServletResponse response,Map colMap,Map colPd) {
		ServletOutputStream out = null;
		try {
			/** 创建通用EXCEL 开始 */
			HSSFWorkbook tWb = new HSSFWorkbook();
			Region region = null;
			HSSFSheet tSheet = null;
			HSSFRow tRow = null;
			Vector vec2 = (Vector) vec.get(0);
			int len = vec2.size();
			Set set = colMap.keySet();
			int colNum = set.size();
			HSSFCell [] cs = new HSSFCell [colNum+1];
//			HSSFCell c0 = null;
//			HSSFCell c1 = null;
//			HSSFCell c2 = null;
//			HSSFCell c3 = null;
			HSSFFont font = tWb.createFont();
			font.setFontHeightInPoints((short) 9);
			font.setFontName("宋体");
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			HSSFCellStyle cStyle = tWb.createCellStyle();
			cStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			HSSFCellStyle cStyleM = tWb.createCellStyle();
			cStyleM.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cStyleM.setFont(font);
			HSSFCellStyle cStyleM_R = tWb.createCellStyle();
			cStyleM_R.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			cStyleM_R.setFont(font);
			/** 创建通用EXCEL 结束 */

			/** 创建Sheet 开始 */
			tSheet = tWb.createSheet();

			tWb.setSheetName(0, title1, HSSFCell.ENCODING_UTF_16);
			for (int i=0;i<colNum;i++) {
				tSheet.setColumnWidth((short) i, (short) (16 * 256));
			}
//			tSheet.setColumnWidth((short) 0, (short) (16 * 256));
//			tSheet.setColumnWidth((short) 1, (short) (16 * 256));
//			tSheet.setColumnWidth((short) 2, (short) (22 * 256));
			// tSheet.setColumnWidth((short) 11, (short) (14 * 256));
 
			// 标题行
			tRow = tSheet.createRow((short) 0);
			cs[0] = createCell(tWb, tRow, (short) 5, title1,null);
			cs[0].setCellStyle(cStyleM);
			// 日期行
			tRow = tSheet.createRow((short) 1);
			region = new Region(1, (short) 9, 1, (short) 13);
			tSheet.addMergedRegion(region);
//			createCell(tWb, tRow, (short) 9, "               " + mydate + " 至 "
//					+ getUpDay(0), HSSFCellStyle.ALIGN_CENTER, false);

			// 表头行
			tRow = tSheet.createRow((short) 2);
			// c0 = createCell(tWb, tRow, (short) 11, "");
			Iterator it = set.iterator();
			for (int i=1;i<cs.length;i++) {
				
				while(it.hasNext()){
					int j = Integer.parseInt(it.next()+"");
					if(i==j+1){
						String colName = colMap.get(j+"")+"";
						cs[i] = createCell(tWb, tRow, (short) (i-1),colName ,null);
						break;
					}
				}
			}
//			cs[1] = createCell(tWb, tRow, (short) 0, "产品编码",null);
//			cs[2] = createCell(tWb, tRow, (short) 1, "平均出库",null);
//			cs[3] = createCell(tWb, tRow, (short) 2, "当前库存",null);
			
			for (int i = 1; i < cs.length; i++) {
				cs[i].setCellStyle(cStyleM_R);
			}

//			cs[1].setCellStyle(cStyleM_R);
//			cs[2].setCellStyle(cStyleM_R);
//			cs[3].setCellStyle(cStyleM_R);
			// c0.setCellStyle(cStyleM_R);
			short index = 2;
			BigDecimal count=null;
			String cou = "";
			if (vec != null && vec.size() > 0) {
				for (int k = 0; k < vec.size(); k++) {
					Vector vec1 = (Vector) vec.get(k);
					if (vec1 == null || !(vec1.size() > 0)) {
						break;
					}
					
					for (int j = 0; j < vec1.size(); j++) {
						String[] values = (String[]) vec1.get(j);
						
//						count = new BigDecimal(values[2]);
//						cou  = count.divide(new BigDecimal(request.getParameter("ibs")), 2,BigDecimal.ROUND_HALF_UP).toString();
						tRow = tSheet.createRow((short) (index + 1));
						Set set2 = colMap.keySet();
						Iterator iter = set2.iterator();
						for (int i = 0; i < values.length; i++) {
							while(iter.hasNext()){
								int num= Integer.parseInt((iter.next()+""));
								if(i==num){
									String createType = colPd.get(num+"")+"";
									if(createType.equals("文本")){
										createCell(tWb, tRow, (short) i,
											values[i] == null ? "" : values[i],values);
									}else if(createType.equals("整数")){
										createCell2(tWb, tRow, (short) i,
												values[i] == null ? "" : values[i],values);
									}else if(createType.equals("小数保留2")){
										createCell3(tWb, tRow, (short) i,
												values[i] == null ? "" : values[i],values);
									}
								break;
								}
							}
						}
//						createCell(tWb, tRow, (short) 0,
//								values[0] == null ? "" : values[0],values);
//						createCell3(tWb, tRow, (short) 1,
//								values[1] == null ? "" : values[1],values);
//						createCell3(tWb, tRow, (short) 2,
//								values[2] == null ? "" : cou,values);
						index++;

					}
				}
				/** 输出EXCEL文件 开始 */
				fileName = fileName + ".xls";
				response.setContentType("application/ms-excel");
				response.addHeader("Content-disposition", "filename="
						+ fileName);
				out = response.getOutputStream();
				tWb.write(out);
				/** 输出EXCEL文件 结束 */
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {

					e.printStackTrace();
				}
			}
		}
	}
	
	
	
	
}
